package com.tianqu.tms.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.tianqu.tms.core.exception.TmsDaoException;
import com.tianqu.tms.dto.MarketCarInfoDto;
import com.tianqu.tms.dto.TransferPersonalCarInfoDto;
/**
 * 
 * 非库存车辆基本信息
 * 
 * @author Administrator
 *
 */
public class TransferPersonalCarInfoDaoImp extends TmsDao implements
		TransferPersonalCarInfoDao {

	protected TransferPersonalCarInfoDaoImp() throws TmsDaoException {
		super();
		// TODO Auto-generated constructor stub
	}

	/**
	 * 
	 * 评估插入
	 */
	@Override
	public void insertCarBaseInfoAtAssess(TransferPersonalCarInfoDto dto)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;	

		try {
			StringBuffer sql = new StringBuffer("INSERT INTO  ");
			sql.append(TmsTableNames.TMS_TRANSFER_PERSONALCAR_INFO);
			sql.append(" (  ");
			sql.append(" SERIAL,  ");
			sql.append(" STATUS,  ");
			sql.append(" UPDATEUSERID,  ");
			sql.append(" CARDTYPE,  ");
			sql.append(" CAROWNER,  ");
			
			sql.append(" OWNERTYPE,  ");
			sql.append(" LICENSEPLATE,  ");
			sql.append(" INSERTTIME,  ");
			sql.append(" INSERTUSERID,  ");
			sql.append(" UPDATETIME, ");
			sql.append(" VIN, ");
			sql.append(" PHOTO_1, ");
			sql.append(" PHOTO_2, ");
			sql.append(" PHOTO_3, ");
			sql.append(" PHOTO_4  ");
			
			sql.append(" )  ");
			sql.append(" VALUES  ");
			sql.append(" (?,?,?,?,?,?,?,SYSDATE(),?,SYSDATE(),?,?,?,?,?) ");
			ps = conn.prepareStatement(sql.toString());

			ps.setString(1, dto.getSerial());
			ps.setInt(2, dto.getStatus());
			ps.setInt(3, dto.getUpdateUserId());
			ps.setInt(4, dto.getCardType());
			ps.setString(5, dto.getCarOwner());
			
			ps.setInt(6, dto.getOwnerType());
			ps.setString(7, dto.getLicensePlate());
			ps.setInt(8, dto.getUpdateUserId());
			ps.setString(9, dto.getVin());
			
			ps.setString(10, dto.getPhoto_1());
			ps.setString(11, dto.getPhoto_2());
			ps.setString(12, dto.getPhoto_3());
			ps.setString(13, dto.getPhoto_4());
			
			ps.executeUpdate();	
			
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}						
	}

	@Override
	public void updatePersonInfobySerial(TransferPersonalCarInfoDto dto)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE ");
		sql.append(TmsTableNames.TMS_TRANSFER_PERSONALCAR_INFO);
		sql.append(" SET ");
		sql.append(" CAROWNER=?,");
		sql.append(" OWNERTYPE=?, ");
		sql.append(" UPDATEUSERID=?, ");
		sql.append(" UPDATETIME=SYSDATE(), ");
		sql.append(" STATUS=?  ");
		sql.append(" WHERE ");
		sql.append(" SERIAL=? ");
		try {
			
			ps = conn.prepareStatement(sql.toString());
			
			ps.setString(1, dto.getCarOwner());
			ps.setInt(2, dto.getOwnerType());
			ps.setInt(3, dto.getUpdateUserId());
			ps.setInt(4, dto.getStatus());
			ps.setString(5, dto.getSerial());
			
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			super.close(rs, ps);
		}	
		
	}

	@Override
	public void updateCarBaseInfoAtAssess(TransferPersonalCarInfoDto dto)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;			
		try {
			StringBuffer sql = new StringBuffer(" UPDATE  ");
			sql.append(TmsTableNames.TMS_TRANSFER_PERSONALCAR_INFO);
			sql.append(" SET  ");
			sql.append(" LICENSEPLATE=?,  ");
			sql.append(" VIN=?,  ");
			sql.append(" VENDORCODE=?,  ");
			sql.append(" MODELCODE=?,  ");
			sql.append(" CARTYPE=?,  ");
			sql.append(" COLOR=?,  ");
			sql.append(" CARUSE=?,  ");
			sql.append(" REGDATE=?,  ");
			sql.append(" SENDCARDDATE=?,  ");
			sql.append(" UPDATEUSERID=?,  ");
			sql.append(" CAROWNER=?,  ");
			sql.append(" UPDATETIME=SYSDATE(),  ");
			sql.append(" PRICE=?  ");
			sql.append(" WHERE  ");
			sql.append(" SERIAL=? ");		

			ps = conn.prepareStatement(sql.toString());

			ps.setString(1, dto.getLicensePlate());
			ps.setString(2, dto.getVin());
			ps.setString(3, dto.getVendorCode());
			ps.setString(4, dto.getModelCode());
			ps.setInt(5, dto.getCarType());
			ps.setInt(6, dto.getColor());
			ps.setInt(7, dto.getCarUse());
			ps.setString(8, dto.getRegDate());
			ps.setString(9, dto.getSendCardDate());
			ps.setInt(10, dto.getUpdateUserId());
			ps.setString(11, dto.getCarOwner());
			ps.setInt(12, dto.getPrice());
			ps.setString(13, dto.getSerial());
			
			ps.executeUpdate();	
			
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}			
	}

	
	
	@Override
	public TransferPersonalCarInfoDto getPersonalCarInfoByLicensePlate(String licensePlate) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		TransferPersonalCarInfoDto dto = null;
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT SERIAL,");
		sql.append("ID,");
		sql.append("CAROWNER,");
		sql.append("VIN,");
		sql.append("ENGINE,");
		sql.append("LICENSEPLATE,");
		sql.append("PRICE,");
		sql.append("COLOR,");
		sql.append("MODELCODE,");
		sql.append("VENDORCODE,");
		sql.append("REGDATE,");
		sql.append("YEARCAREFULPERIOD,");
		sql.append("DISPLACEMENT,");
		sql.append("JQX,");
		sql.append("CARUSE,");
		sql.append("CARTYPE,");
		sql.append("MILEAGE,");
		sql.append("GEARBOX,");
		sql.append("SENDCARDDATE,");
		sql.append("CARDTYPE,");
		sql.append("COUNTRYSTYLE,");
		sql.append("REGISTERCARDNO,");
		sql.append("COMPANYCONTACTPERSON,");
		sql.append("COMPANYCONTACTPHONE,");
		sql.append("CAROWNERIDCARDNO,");
		sql.append("CARSTYLEID,");
		sql.append("BRANDID,");
		sql.append("FUELTYPE,");
		sql.append("LOADQUALITY,");
		sql.append("PASSENGER,");
		sql.append("OWNERPHONE,");
		sql.append("OWNERADDRESS,");
		sql.append("ATTRIBUTE,");
		sql.append("OWNERTYPE,");
		sql.append("OWNERORGNO,");
		sql.append("UPDATETIME,");
		sql.append("UPDATEUSERID, ");
		sql.append("PHOTO_1, ");
		sql.append("PHOTO_2, ");
		sql.append("PHOTO_3, ");
		sql.append("PHOTO_4, ");
		sql.append("PHOTO_5, ");
		sql.append("PHOTO_6, ");
		sql.append("PHOTO_7, ");
		sql.append("PHOTO_8, ");
		sql.append("INSERTUSERID, ");
		sql.append("VENDORID, ");
		sql.append("ATTESTSTATUS, ");
		sql.append("STATUS, ");
		sql.append("CARNAME, ");
		sql.append("INSERTTIME ");
		sql.append("FROM ");
		sql.append(TmsTableNames.TMS_TRANSFER_PERSONALCAR_INFO);
		sql.append(" WHERE " );
		sql.append(" LICENSEPLATE='"+licensePlate+"'");
		
		
		try {
			ps = conn.prepareStatement(sql.toString());		
			rs = ps.executeQuery();
		
			while (rs.next()) {		
				dto = new TransferPersonalCarInfoDto();
				dto.setCarOwner(rs.getString("CAROWNER"));
				dto.setId(rs.getInt("ID"));
				dto.setSerial(rs.getString("SERIAL"));
				dto.setVin(rs.getString("VIN"));
				dto.setEngine(rs.getString("ENGINE"));
				dto.setLicensePlate(rs.getString("LICENSEPLATE"));
				dto.setPrice(rs.getInt("PRICE"));
				dto.setColor(rs.getInt("COLOR"));
				dto.setModelCode(rs.getString("MODELCODE"));
				dto.setVendorCode(rs.getString("VENDORCODE"));
				dto.setRegDate(rs.getString("REGDATE"));
				dto.setYearCarefulPeriod(rs.getString("YEARCAREFULPERIOD"));
				dto.setDisplacement(rs.getInt("DISPLACEMENT"));
				dto.setJqx(rs.getString("JQX"));
				dto.setCarUse(rs.getInt("CARUSE"));
				dto.setCarType(rs.getInt("CARTYPE"));
				dto.setMileage(rs.getInt("MILEAGE"));
				dto.setGearbox(rs.getInt("GEARBOX"));
				dto.setSendCardDate(rs.getString("SENDCARDDATE"));
				dto.setCardType(rs.getInt("CARDTYPE"));
				dto.setCountryStyle(rs.getInt("COUNTRYSTYLE"));
				dto.setRegisterCardNo(rs.getString("REGISTERCARDNO"));
				dto.setCompanyContactPerson(rs.getString("COMPANYCONTACTPERSON"));
				dto.setCompanyContactPhone(rs.getString("COMPANYCONTACTPHONE"));
				dto.setCarOwnerIdCardNo(rs.getString("CAROWNERIDCARDNO"));
				dto.setCarStyleId(rs.getString("CARSTYLEID"));
				dto.setBrandId(rs.getString("BRANDID"));
				dto.setFuelType(rs.getInt("FUELTYPE"));
				dto.setLoadquality(rs.getInt("LOADQUALITY"));
				dto.setPassenger(rs.getInt("PASSENGER"));
				dto.setOwnerPhone(rs.getString("OWNERPHONE"));
				dto.setOwnerAddress(rs.getString("OWNERADDRESS"));				
				dto.setAttribute(rs.getInt("ATTRIBUTE"));
				dto.setOwnerType(rs.getInt("OWNERTYPE"));
				dto.setOwnerOrgNo(rs.getString("OWNERORGNO"));
				dto.setUpdateTime(rs.getTimestamp("UPDATETIME"));
				dto.setUpdateUserId(rs.getInt("UPDATEUSERID"));
				dto.setPhoto_1(rs.getString("PHOTO_1"));
				dto.setPhoto_2(rs.getString("PHOTO_2"));
				dto.setPhoto_3(rs.getString("PHOTO_3"));
				dto.setPhoto_4(rs.getString("PHOTO_4"));
				dto.setPhoto_5(rs.getString("PHOTO_5"));
				dto.setPhoto_6(rs.getString("PHOTO_6"));
				dto.setPhoto_7(rs.getString("PHOTO_7"));
				dto.setPhoto_8(rs.getString("PHOTO_8"));
				dto.setInsertUserId(rs.getInt("INSERTUSERID"));
				dto.setVendorId(rs.getString("VENDORID"));
				dto.setStatus(rs.getInt("STATUS"));
				dto.setAttestStatus(rs.getInt("attestStatus"));  
				dto.setCarName(rs.getString("CARNAME"));
				dto.setInsertTime(rs.getTimestamp("INSERTTIME"));
			}
			return dto;
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}
	
	
	

	@Override
	public TransferPersonalCarInfoDto getPersonalCarInfoByVin(String vin) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		TransferPersonalCarInfoDto dto = null;
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT SERIAL,");
		sql.append("ID,");
		sql.append("CAROWNER,");
		sql.append("VIN,");
		sql.append("ENGINE,");
		sql.append("LICENSEPLATE,");
		sql.append("PRICE,");
		sql.append("COLOR,");
		sql.append("MODELCODE,");
		sql.append("VENDORCODE,");
		sql.append("REGDATE,");
		sql.append("YEARCAREFULPERIOD,");
		sql.append("DISPLACEMENT,");
		sql.append("JQX,");
		sql.append("CARUSE,");
		sql.append("CARTYPE,");
		sql.append("MILEAGE,");
		sql.append("GEARBOX,");
		sql.append("SENDCARDDATE,");
		sql.append("CARDTYPE,");
		sql.append("COUNTRYSTYLE,");
		sql.append("REGISTERCARDNO,");
		sql.append("COMPANYCONTACTPERSON,");
		sql.append("COMPANYCONTACTPHONE,");
		sql.append("CAROWNERIDCARDNO,");
		sql.append("CARSTYLEID,");
		sql.append("BRANDID,");
		sql.append("FUELTYPE,");
		sql.append("LOADQUALITY,");
		sql.append("PASSENGER,");
		sql.append("OWNERPHONE,");
		sql.append("OWNERADDRESS,");
		sql.append("ATTRIBUTE,");
		sql.append("OWNERTYPE,");
		sql.append("OWNERORGNO,");
		sql.append("UPDATETIME,");
		sql.append("UPDATEUSERID, ");
		sql.append("PHOTO_1, ");
		sql.append("PHOTO_2, ");
		sql.append("PHOTO_3, ");
		sql.append("PHOTO_4, ");
		sql.append("PHOTO_5, ");
		sql.append("PHOTO_6, ");
		sql.append("PHOTO_7, ");
		sql.append("PHOTO_8, ");
		sql.append("INSERTUSERID, ");
		sql.append("VENDORID, ");
		sql.append("ATTESTSTATUS, ");
		sql.append("STATUS, ");
		sql.append("CARNAME, ");
		sql.append("INSERTTIME ");
		sql.append("FROM ");
		sql.append(TmsTableNames.TMS_TRANSFER_PERSONALCAR_INFO);
		sql.append(" WHERE " );
		sql.append(" VIN=?");
		try {
			ps = conn.prepareStatement(sql.toString());		
			ps.setString(1, vin);
			rs = ps.executeQuery();
		
			while (rs.next()) {		
				dto = new TransferPersonalCarInfoDto();
				dto.setCarOwner(rs.getString("CAROWNER"));
				dto.setId(rs.getInt("ID"));
				dto.setSerial(rs.getString("SERIAL"));
				dto.setVin(rs.getString("VIN"));
				dto.setEngine(rs.getString("ENGINE"));
				dto.setLicensePlate(rs.getString("LICENSEPLATE"));
				dto.setPrice(rs.getInt("PRICE"));
				dto.setColor(rs.getInt("COLOR"));
				dto.setModelCode(rs.getString("MODELCODE"));
				dto.setVendorCode(rs.getString("VENDORCODE"));
				dto.setRegDate(rs.getString("REGDATE"));
				dto.setYearCarefulPeriod(rs.getString("YEARCAREFULPERIOD"));
				dto.setDisplacement(rs.getInt("DISPLACEMENT"));
				dto.setJqx(rs.getString("JQX"));
				dto.setCarUse(rs.getInt("CARUSE"));
				dto.setCarType(rs.getInt("CARTYPE"));
				dto.setMileage(rs.getInt("MILEAGE"));
				dto.setGearbox(rs.getInt("GEARBOX"));
				dto.setSendCardDate(rs.getString("SENDCARDDATE"));
				dto.setCardType(rs.getInt("CARDTYPE"));
				dto.setCountryStyle(rs.getInt("COUNTRYSTYLE"));
				dto.setRegisterCardNo(rs.getString("REGISTERCARDNO"));
				dto.setCompanyContactPerson(rs.getString("COMPANYCONTACTPERSON"));
				dto.setCompanyContactPhone(rs.getString("COMPANYCONTACTPHONE"));
				dto.setCarOwnerIdCardNo(rs.getString("CAROWNERIDCARDNO"));
				dto.setCarStyleId(rs.getString("CARSTYLEID"));
				dto.setBrandId(rs.getString("BRANDID"));
				dto.setFuelType(rs.getInt("FUELTYPE"));
				dto.setLoadquality(rs.getInt("LOADQUALITY"));
				dto.setPassenger(rs.getInt("PASSENGER"));
				dto.setOwnerPhone(rs.getString("OWNERPHONE"));
				dto.setOwnerAddress(rs.getString("OWNERADDRESS"));				
				dto.setAttribute(rs.getInt("ATTRIBUTE"));
				dto.setOwnerType(rs.getInt("OWNERTYPE"));
				dto.setOwnerOrgNo(rs.getString("OWNERORGNO"));
				dto.setUpdateTime(rs.getTimestamp("UPDATETIME"));
				dto.setUpdateUserId(rs.getInt("UPDATEUSERID"));
				dto.setPhoto_1(rs.getString("PHOTO_1"));
				dto.setPhoto_2(rs.getString("PHOTO_2"));
				dto.setPhoto_3(rs.getString("PHOTO_3"));
				dto.setPhoto_4(rs.getString("PHOTO_4"));
				dto.setPhoto_5(rs.getString("PHOTO_5"));
				dto.setPhoto_6(rs.getString("PHOTO_6"));
				dto.setPhoto_7(rs.getString("PHOTO_7"));
				dto.setPhoto_8(rs.getString("PHOTO_8"));
				dto.setInsertUserId(rs.getInt("INSERTUSERID"));
				dto.setVendorId(rs.getString("VENDORID"));
				dto.setStatus(rs.getInt("STATUS"));
				dto.setAttestStatus(rs.getInt("attestStatus"));  
				dto.setCarName(rs.getString("CARNAME"));
				dto.setInsertTime(rs.getTimestamp("INSERTTIME"));
			}
			return dto;
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}

	/**
	 * 待办部分
	 */
	@Override
	public TransferPersonalCarInfoDto getPersonalCarInfoBySerial(String serial) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		TransferPersonalCarInfoDto dto = null;
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT SERIAL,");
		sql.append("ID,");
		sql.append("CAROWNER,");
		sql.append("VIN,");
		sql.append("ENGINE,");
		sql.append("LICENSEPLATE,");
		sql.append("PRICE,");
		sql.append("COLOR,");
		sql.append("MODELCODE,");
		sql.append("VENDORCODE,");
		sql.append("REGDATE,");
		sql.append("YEARCAREFULPERIOD,");
		sql.append("DISPLACEMENT,");
		sql.append("JQX,");
		sql.append("CARUSE,");
		sql.append("CARTYPE,");
		sql.append("MILEAGE,");
		sql.append("GEARBOX,");
		sql.append("SENDCARDDATE,");
		sql.append("CARDTYPE,");
		sql.append("COUNTRYSTYLE,");
		sql.append("REGISTERCARDNO,");
		sql.append("COMPANYCONTACTPERSON,");
		sql.append("COMPANYCONTACTPHONE,");
		sql.append("CAROWNERIDCARDNO,");
		sql.append("CARSTYLEID,");
		sql.append("BRANDID,");
		sql.append("FUELTYPE,");
		sql.append("LOADQUALITY,");
		sql.append("PASSENGER,");
		sql.append("OWNERPHONE,");
		sql.append("OWNERADDRESS,");
		sql.append("ATTRIBUTE,");
		sql.append("OWNERTYPE,");
		sql.append("OWNERORGNO,");
		sql.append("UPDATETIME,");
		sql.append("UPDATEUSERID, ");
		sql.append("PHOTO_1, ");
		sql.append("PHOTO_2, ");
		sql.append("PHOTO_3, ");
		sql.append("PHOTO_4, ");
		sql.append("PHOTO_5, ");
		sql.append("PHOTO_6, ");
		sql.append("PHOTO_7, ");
		sql.append("PHOTO_8, ");
		sql.append("INSERTUSERID, ");
		sql.append("VENDORID, ");
		sql.append("ATTESTSTATUS, ");
		sql.append("STATUS, ");
		sql.append("CARNAME, ");
		sql.append("INSERTTIME ");
		sql.append("FROM ");
		sql.append(TmsTableNames.TMS_TRANSFER_PERSONALCAR_INFO);
		sql.append(" WHERE " );
		sql.append(" SERIAL=?");
		
		
		try {
			ps = conn.prepareStatement(sql.toString());		
			ps.setString(1, serial);
			rs = ps.executeQuery();
		
			while (rs.next()) {		
				dto = new TransferPersonalCarInfoDto();
				dto.setCarOwner(rs.getString("CAROWNER"));
				dto.setId(rs.getInt("ID"));
				dto.setSerial(rs.getString("SERIAL"));
				dto.setVin(rs.getString("VIN"));
				dto.setEngine(rs.getString("ENGINE"));
				dto.setLicensePlate(rs.getString("LICENSEPLATE"));
				dto.setPrice(rs.getInt("PRICE"));
				dto.setColor(rs.getInt("COLOR"));
				dto.setModelCode(rs.getString("MODELCODE"));
				dto.setVendorCode(rs.getString("VENDORCODE"));
				dto.setRegDate(rs.getString("REGDATE"));
				dto.setYearCarefulPeriod(rs.getString("YEARCAREFULPERIOD"));
				dto.setDisplacement(rs.getInt("DISPLACEMENT"));
				dto.setJqx(rs.getString("JQX"));
				dto.setCarUse(rs.getInt("CARUSE"));
				dto.setCarType(rs.getInt("CARTYPE"));
				dto.setMileage(rs.getInt("MILEAGE"));
				dto.setGearbox(rs.getInt("GEARBOX"));
				dto.setSendCardDate(rs.getString("SENDCARDDATE"));
				dto.setCardType(rs.getInt("CARDTYPE"));
				dto.setCountryStyle(rs.getInt("COUNTRYSTYLE"));
				dto.setRegisterCardNo(rs.getString("REGISTERCARDNO"));
				dto.setCompanyContactPerson(rs.getString("COMPANYCONTACTPERSON"));
				dto.setCompanyContactPhone(rs.getString("COMPANYCONTACTPHONE"));
				dto.setCarOwnerIdCardNo(rs.getString("CAROWNERIDCARDNO"));
				dto.setCarStyleId(rs.getString("CARSTYLEID"));
				dto.setBrandId(rs.getString("BRANDID"));
				dto.setFuelType(rs.getInt("FUELTYPE"));
				dto.setLoadquality(rs.getInt("LOADQUALITY"));
				dto.setPassenger(rs.getInt("PASSENGER"));
				dto.setOwnerPhone(rs.getString("OWNERPHONE"));
				dto.setOwnerAddress(rs.getString("OWNERADDRESS"));				
				dto.setAttribute(rs.getInt("ATTRIBUTE"));
				dto.setOwnerType(rs.getInt("OWNERTYPE"));
				dto.setOwnerOrgNo(rs.getString("OWNERORGNO"));
				dto.setUpdateTime(rs.getTimestamp("UPDATETIME"));
				dto.setUpdateUserId(rs.getInt("UPDATEUSERID"));
				dto.setPhoto_1(rs.getString("PHOTO_1"));
				dto.setPhoto_2(rs.getString("PHOTO_2"));
				dto.setPhoto_3(rs.getString("PHOTO_3"));
				dto.setPhoto_4(rs.getString("PHOTO_4"));
				dto.setPhoto_5(rs.getString("PHOTO_5"));
				dto.setPhoto_6(rs.getString("PHOTO_6"));
				dto.setPhoto_7(rs.getString("PHOTO_7"));
				dto.setPhoto_8(rs.getString("PHOTO_8"));
				dto.setInsertUserId(rs.getInt("INSERTUSERID"));
				dto.setVendorId(rs.getString("VENDORID"));
				dto.setStatus(rs.getInt("STATUS"));
				dto.setAttestStatus(rs.getInt("attestStatus"));  
				dto.setCarName(rs.getString("CARNAME"));
				dto.setInsertTime(rs.getTimestamp("INSERTTIME"));
			}
			return dto;
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
		
	}
	
	/**
	 * 删除
	 */
	@Override
	public void deletePersonalInfoBySerial(String serial) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			StringBuffer sql = new StringBuffer("");
			sql.append("DELETE ");
			sql.append("FROM ");
			sql.append(TmsTableNames.TMS_TRANSFER_PERSONALCAR_INFO);
			sql.append(" WHERE ");
			sql.append(" SERIAL=?");
			ps.setString(1, serial);
			ps = conn.prepareStatement(sql.toString());
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			close(rs, ps);
		}		
	}

	@Override
	public void updateCarBaseInfo(TransferPersonalCarInfoDto dto)
			throws TmsDaoException {

		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE ");
		sql.append(TmsTableNames.TMS_TRANSFER_PERSONALCAR_INFO);
		sql.append(" SET ");
		sql.append(" CARNAME=?,");
		sql.append(" GEARBOX=?,");
		sql.append(" MODELCODE=?,");
		sql.append(" VENDORCODE=?,");
		sql.append(" CARTYPE=?,");
		sql.append(" ENGINE=?,");
		sql.append(" COLOR=?,");
		sql.append(" COUNTRYSTYLE=?,");
		sql.append(" DISPLACEMENT=?,");
		sql.append(" FUELTYPE=?,");
		sql.append(" PASSENGER=?,");
		sql.append(" LOADQUALITY=?,");
		sql.append(" CARUSE=?,");
		sql.append(" CARDTYPE=?,");
		sql.append(" YEARCAREFULPERIOD=?,");
		sql.append(" JQX=?,");
		sql.append(" REGISTERCARDNO=?,");
		sql.append(" REGDATE=?,");
		sql.append(" SENDCARDDATE=?,");
		sql.append(" OWNERTYPE=?,");
		sql.append(" OWNERPHONE=?,");
		sql.append(" CAROWNER=?,");
		sql.append(" CAROWNERIDCARDNO=?,");
		sql.append(" OWNERADDRESS=?,");
		sql.append(" CARSTYLEID=?,");
		sql.append(" BRANDID=?,");
		sql.append(" VENDORID=?,");
		sql.append(" UPDATETIME=SYSDATE(),");
		sql.append(" UPDATEUSERID=?, ");
		sql.append(" MILEAGE=?, ");
		sql.append(" OWNERORGNO=?, ");
		sql.append(" STATUS=?, "); 
		sql.append(" VIN=?, "); 
		sql.append(" PHOTO_1=?, ");
		sql.append(" PHOTO_2=?, ");
		sql.append(" PHOTO_3=?, ");
		sql.append(" PHOTO_4=?  ");
		sql.append(" WHERE ");
		sql.append(" SERIAL=? ");
		try {
			
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, dto.getCarName());
			ps.setLong(2, dto.getGearbox());
			ps.setString(3, dto.getModelCode());
			ps.setString(4, dto.getVendorCode());
			ps.setInt(5, dto.getCarType());
			ps.setString(6, dto.getEngine());
			ps.setInt(7, dto.getColor());
			ps.setInt(8, dto.getCountryStyle());
			ps.setInt(9, dto.getDisplacement());
			ps.setLong(10, dto.getFuelType());
			ps.setInt(11, dto.getPassenger());
			ps.setInt(12, dto.getLoadquality());
			ps.setInt(13, dto.getCarUse());
			ps.setInt(14, dto.getCardType());
			ps.setString(15, dto.getYearCarefulPeriod());
			ps.setString(16, dto.getJqx());
			ps.setString(17, dto.getRegisterCardNo());
			ps.setString(18, dto.getRegDate());
			ps.setString(19, dto.getSendCardDate());
			ps.setInt(20, dto.getOwnerType());
			ps.setString(21, dto.getOwnerPhone());
			ps.setString(22, dto.getCarOwner());
			ps.setString(23, dto.getCarOwnerIdCardNo());
			ps.setString(24, dto.getOwnerAddress());
			ps.setString(25, dto.getCarStyleId());
			ps.setString(26, dto.getBrandId());
			ps.setString(27, dto.getVendorId());
			ps.setInt(28, dto.getUpdateUserId());
			ps.setInt(29, dto.getMileage());
			ps.setString(30, dto.getOwnerOrgNo());
			ps.setInt(31, dto.getStatus());
			ps.setString(32, dto.getVin());
			ps.setString(33, dto.getPhoto_1());
			ps.setString(34, dto.getPhoto_2());
			ps.setString(35, dto.getPhoto_3());
			ps.setString(36, dto.getPhoto_4());
			ps.setString(37, dto.getSerial());
			
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			super.close(rs, ps);
		}
	}
	/**
	 * 待办受理更新库存信息
	 */
	@Override
	public void updateMarketInfoByLicensePlate(TransferPersonalCarInfoDto dto)
			throws TmsDaoException {

		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE ");
		sql.append(TmsTableNames.TMS_TRANSFER_PERSONALCAR_INFO);
		sql.append(" SET ");
		sql.append(" CARNAME=?,");
		sql.append(" GEARBOX=?,");
		sql.append(" MODELCODE=?,");
		sql.append(" VENDORCODE=?,");
		sql.append(" CARTYPE=?,");
		sql.append(" ENGINE=?,");
		sql.append(" COLOR=?,");
		sql.append(" COUNTRYSTYLE=?,");
		sql.append(" DISPLACEMENT=?,");
		sql.append(" FUELTYPE=?,");
		sql.append(" PASSENGER=?,");
		sql.append(" LOADQUALITY=?,");
		sql.append(" CARUSE=?,");
		sql.append(" CARDTYPE=?,");
		sql.append(" YEARCAREFULPERIOD=?,");
		sql.append(" JQX=?,");
		sql.append(" REGISTERCARDNO=?,");
		sql.append(" REGDATE=?,");
		sql.append(" SENDCARDDATE=?,");
		sql.append(" OWNERTYPE=?,");
		sql.append(" OWNERPHONE=?,");
		sql.append(" CAROWNER=?,");
		sql.append(" CAROWNERIDCARDNO=?,");
		sql.append(" OWNERADDRESS=?,");
		sql.append(" CARSTYLEID=?,");
		sql.append(" BRANDID=?,");
		sql.append(" VENDORID=?,");
		sql.append(" UPDATETIME=SYSDATE(),");
		sql.append(" UPDATEUSERID=?, ");
		sql.append(" MILEAGE=?, ");
		sql.append(" OWNERORGNO=?, ");
		sql.append(" STATUS=?, "); 
		sql.append(" VIN=? "); 
		sql.append(" WHERE ");
		sql.append(" SERIAL=? ");
		try {
			
			ps = conn.prepareStatement(sql.toString());
			
			ps.setString(1, dto.getCarName());
			ps.setLong(2, dto.getGearbox());
			ps.setString(3, dto.getModelCode());
			ps.setString(4, dto.getVendorCode());
			ps.setInt(5, dto.getCarType());
			ps.setString(6, dto.getEngine());
			ps.setInt(7, dto.getColor());
			ps.setInt(8, dto.getCountryStyle());
			ps.setInt(9, dto.getDisplacement());
			ps.setLong(10, dto.getFuelType());
			ps.setInt(11, dto.getPassenger());
			ps.setInt(12, dto.getLoadquality());
			ps.setInt(13, dto.getCarUse());
			ps.setInt(14, dto.getCardType());
			ps.setString(15, dto.getYearCarefulPeriod());
			ps.setString(16, dto.getJqx());
			ps.setString(17, dto.getRegisterCardNo());
			ps.setString(18, dto.getRegDate());
			ps.setString(19, dto.getSendCardDate());
			ps.setInt(20, dto.getOwnerType());
			ps.setString(21, dto.getOwnerPhone());
			ps.setString(22, dto.getCarOwner());
			ps.setString(23, dto.getCarOwnerIdCardNo());
			ps.setString(24, dto.getOwnerAddress());
			ps.setString(25, dto.getCarStyleId());
			ps.setString(26, dto.getBrandId());
			ps.setString(27, dto.getVendorId());
			ps.setInt(28, dto.getUpdateUserId());
			ps.setInt(29, dto.getMileage());
			ps.setString(30, dto.getOwnerOrgNo());
			ps.setInt(31, dto.getStatus());
			ps.setString(32, dto.getVin());
			ps.setString(33, dto.getSerial());
			
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			super.close(rs, ps);
		}
	}
	
	
	
	@Override
	public void updateTransferPsersonalStatusByLicensePlate(int status,String licensePlate) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE ");
		sql.append(TmsTableNames.TMS_TRANSFER_PERSONALCAR_INFO);
		sql.append(" SET ");
		sql.append(" UPDATETIME=SYSDATE(),");
		sql.append(" STATUS=? "); 
		sql.append(" WHERE ");
		sql.append(" LICENSEPLATE=? ");
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, status);
			ps.setString(2,licensePlate);
			
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		}finally {
			super.close(rs, ps);
		}		
	}

	@Override
	public void updateCarStatus(TransferPersonalCarInfoDto dto) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE ");
		sql.append(TmsTableNames.TMS_TRANSFER_PERSONALCAR_INFO);
		sql.append(" SET ");
		sql.append(" UPDATETIME=SYSDATE(),");
		sql.append(" UPDATEUSERID=?, ");
		sql.append(" STATUS=? "); 
		sql.append(" WHERE ");
		sql.append(" SERIAL=? ");
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, dto.getUpdateUserId());
			ps.setInt(2, dto.getStatus());
			ps.setString(3, dto.getSerial());
			
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		}finally {
			super.close(rs, ps);
		}		
	}

	@Override
	public void updateLicenseBySerial(TransferPersonalCarInfoDto dto)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			StringBuffer sql = new StringBuffer("UPDATE  ");
			sql.append(TmsTableNames.TMS_TRANSFER_PERSONALCAR_INFO);
			sql.append(" SET  ");
			sql.append(" LICENSEPLATE=?,   ");
			sql.append(" UPDATETIME=SYSDATE() ,  ");
			sql.append(" UPDATEUSERID=?  ");
			sql.append(" WHERE SERIAL =?  ");

			ps = conn.prepareStatement(sql.toString());
			
			ps.setString(1, dto.getLicensePlate());
			ps.setInt(2, dto.getUpdateUserId());
			ps.setString(3, dto.getSerial());

			ps.executeUpdate();

		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}			
	}

	@Override
	public void updateCarNameS(TransferPersonalCarInfoDto dto) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("UPDATE  ");
		sql.append(TmsTableNames.TMS_TRANSFER_PERSONALCAR_INFO);
		sql.append(" SET  ");
		sql.append(" CARNAME=?,   ");
		sql.append(" DISPLACEMENT=?,   ");
		sql.append(" GEARBOX=?,   ");
		sql.append(" VENDORID=?,   ");
		sql.append(" BRANDID=?,   ");
		sql.append(" CARSTYLEID=?,   ");
		sql.append(" UPDATETIME=SYSDATE() ,  ");
		sql.append(" UPDATEUSERID=?  ");
		sql.append(" WHERE SERIAL =?  ");
		
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, dto.getCarName());
			ps.setInt(2, dto.getDisplacement());
			ps.setInt(3, dto.getGearbox());
			ps.setString(4, dto.getVendorId());
			ps.setString(5, dto.getBrandId());
			ps.setString(6, dto.getCarStyleId());
			ps.setInt(7, dto.getUpdateUserId());
			ps.setString(8, dto.getSerial());
			ps.executeUpdate();
			
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			close(rs, ps);
		}	
	}
	
	@Override
	public int getTransferPersonalCarInfo(String serial) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT status ");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_TRANSFER_PERSONALCAR_INFO);
		sql.append(" WHERE " );
		sql.append(" SERIAL=?");
		try {
			ps = conn.prepareStatement(sql.toString());		
			ps.setString(1, serial);
			rs = ps.executeQuery();
			if(rs.next()) return rs.getInt("status");
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}
		
		return 0;
	}
	
	
	@Override
	public void insertCarBaseInfoAtWX(TransferPersonalCarInfoDto dto) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;	

		try {
			StringBuffer sql = new StringBuffer("INSERT INTO  ");
			sql.append(TmsTableNames.TMS_TRANSFER_PERSONALCAR_INFO);
			sql.append(" (  ");
			sql.append(" SERIAL,  ");
			sql.append(" STATUS,  ");
			if(dto.getLicensePlate() !=null && !dto.getLicensePlate().isEmpty()){
				sql.append(" LICENSEPLATE,  ");
			}else if(dto.getVin() !=null &&!dto.getVin().isEmpty()){
				sql.append(" VIN,  ");
			}
			
			sql.append(" INSERTUSERID,  ");
			sql.append(" UPDATEUSERID,  ");
			sql.append(" INSERTTIME,  ");
			sql.append(" UPDATETIME ");
			sql.append(" )  ");
			sql.append(" VALUES  ");
			sql.append(" (?,?,?,?,?,SYSDATE(),SYSDATE()) ");
			ps = conn.prepareStatement(sql.toString());
			
			ps.setString(1, dto.getSerial());
			ps.setInt(2, dto.getStatus());
			if(dto.getLicensePlate() !=null && !dto.getLicensePlate().isEmpty()){
				ps.setString(3, dto.getLicensePlate());
			}else if(dto.getVin() !=null &&!dto.getVin().isEmpty()){
				ps.setString(3, dto.getVin());
			}
			ps.setInt(4, dto.getInsertUserId());
			ps.setInt(5, dto.getUpdateUserId());
			ps.executeUpdate();	
			
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}						
	}

}
